import pandas as pd
import os
import time
from datetime import date
import xlwings as xw


class abnormal_value:
    def __init__(self):
        self.path = []
        files = os.listdir('./')
        print(files)
        [self.path.append(file) for file in files if 'D94-AE-80' in file]
        [self.path.append(file) for file in files if 'D94-HBT' in file]
        self.path = sorted(self.path)
        print(self.path)
        # 获取文件路径，将两个文件名排序

    def hand_file(self):
        list_sun_sv = []
        list_col = []
        col1 = ['SerialNumber', 'Station ID', 'NOVA1_CR', 'NOVA1_OF', 'NOVA1_T', 'NOVA2_CR', 'NOVA2_OF', 'NOVA2_T',
                'NOVA3_CR', 'NOVA3_OF', 'NOVA3_T', 'R1_CR', 'R1_OF', 'R1_T', 'R2_CR', 'R2_OF', 'R2_T', 'R3_CR', 'R3_OF',
                'R3_T']
        col2 = ['SerialNumber', 'Station ID', 'H1_CR', 'H1_OF', 'H1_T', 'H2_CR', 'H2_OF', 'H2_T', 'H3_CR', 'H3_OF',
                'H3_T', 'VD1_CR', 'VD1_OF', 'VD1_T', 'VD2_CR', 'VD2_OF', 'VD2_T', 'VD3_CR', 'VD3_OF', 'VD3_T', 'VU1_CR',
                'VU1_OF', 'VU1_T', 'VU2_CR', 'VU2_OF', 'VU2_T', 'VU3_CR', 'VU3_OF', 'VU3_T']

        list_col.append(col1)
        list_col.append(col2)
        strs = ['D94-AE-80', 'D94-HBT']
        today = date.today().strftime("%m-%d")  # 得到今天的日期，方便命名
        for n, v in zip(self.path, list_col):  # 得到相应的文件名和列名，开始运行
            df = pd.read_csv(n, usecols=v, header=1, skiprows=[2, 3, 4, 5, 6])
            list_col = df.columns.tolist()
            lsit_SN = []
            for i in list_col[2:]:  # 选取数值列
                data = df[i]
                # 计算上四分位数、下四分位数和四分位距
                Q1 = data.quantile(0.25)
                Q3 = data.quantile(0.75)
                IQR = Q3 - Q1

                # 定义上限和下限
                upper_fence = Q3 + 1.5 * IQR
                lower_fence = Q1 - 1.5 * IQR

                # 标记异常值
                outliers = data[(data < lower_fence) | (data > upper_fence)].values.tolist()
                print(outliers)  # 找到箱线图的异常值
                df_sn = df[df[i].isin(outliers)]  # 找出需要的对应列
                ccol = ['SerialNumber', 'Station ID', i]
                df_sn = df_sn[ccol]
                df_sn.loc[:, df_sn.columns != ccol] = None  # 将我们不需要的值设置为None
                # df_sn = df_sn.sort_values(by=i)
                print(f"【{i}】中存在异常值的SN为:\ n{df_sn['SerialNumber']}")
                lsit_SN.append(df_sn)
            df = pd.concat(lsit_SN, axis=0)
            list_sun_sv.append(df)
        res_file = f'Abnormal_Value_SN{today}.xlsx'
        with pd.ExcelWriter(res_file) as writer:
            for df, s in zip(list_sun_sv, strs):
                df.to_excel(writer, sheet_name=f'{s}', index=False)
        with xw.App(visible=False) as app:
            wb = app.books.open(res_file)
            sht = wb.sheets[0]
            sht.autofit()
            sht2 = wb.sheets[1]
            sht2.autofit()
            wb.save()


if __name__ == '__main__':
    start = time.time()
    av = abnormal_value()
    av.hand_file()
    print(f'程序运行时间为{round(time.time() - start, 2)}秒')
